/**
 * Created by yongwang on 2015/6/15.
 */
var toolUtil = require('../../common/utils/toolUtil');
var async = require('async');
var mysqlUtil = require('../../common/utils/mysqlUtil');
var communityService = require('../communityService');
var Users = mongoose.model('user');
var Community = mongoose.model('community');

Date.prototype.Format = function (fmt) { //author: meizz
    var o = {
        "M+": this.getMonth() + 1, //月份
        "d+": this.getDate(), //日
        "h+": this.getHours(), //小时
        "m+": this.getMinutes(), //分
        "s+": this.getSeconds(), //秒
        "q+": Math.floor((this.getMonth() + 3) / 3), //季度
        "S": this.getMilliseconds() //毫秒
    };
    if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
    for (var k in o)
        if (new RegExp("(" + k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
    return fmt;
};


exports.changeStatus = function (map, callback) {
    var sql = "update score_shop set status = " + map.status + " WHERE id = " + map.id;
    mysqlUtil.query(sql, function (err, result) {
        callback(err, result);
    });
}


exports.getScoreShopList = function (status, page, callback) {
    var offset = (Number(page) - 1) * 10;
    var sql = "";
    if (status == "all") {
        sql = "SELECT * FROM score_shop limit " + offset + ",10";
    }else if (status == "yes") {
        sql = "SELECT * FROM score_shop WHERE status = 1 limit " + offset + ",10";
    }else if (status == "no") {
        sql = "SELECT * FROM score_shop WHERE status = 0 limit " + offset + ",10";
    }

    async.series([
        function (callbackInner) {
            mysqlUtil.query(sql, function (err, result) {
                callbackInner(err, result);
            });
        },
        function (callbackInner) {

            var sql = "";
            if (status == "all") {
                sql = "SELECT COUNT(*) as pages FROM score_shop";
            }else if (status == "yes") {
                sql = "SELECT COUNT(*) as pages FROM score_shop WHERE status = 1";
            }else if (status == "no") {
                sql = "SELECT COUNT(*) as pages FROM score_shop WHERE status = 1";
            }

            mysqlUtil.query(sql, function (err, result) {
                callbackInner(err, result);
            });
        }
    ], callback);
}


exports.searchGoods = function (status, name, callback) {

    var sql = "";
    if (status == "all") {
        sql = "SELECT * FROM score_shop where product_name like '%" + name + "%'";
    }else if (status == "yes") {
        sql = "SELECT * FROM score_shop where product_name like '%" + name + "%'" + "AND status = " + 1;
    }else if (status == "no") {
        sql = "SELECT * FROM score_shop where product_name like '%" + name + "%'" + "AND status = " + 0;
    }

    mysqlUtil.query(sql, function (err, result) {
        callback(err, result);
    });
}

exports.searchValue = function (map, callback) {
    Users.find({
        community: map.communityName,
        nickName: new RegExp(map.userName)
    }, 'nickName mobilePhone roomUnit roomNumber community').populate('community', 'communityName').exec(function (err, user) {
        logger.info(user);
        var i = 0, length = user.length, data = new Array(length);
        user.forEach(function (item) {
            var sql = 'SELECT total_value,current_value FROM score_info WHERE user_id=?';
            mysqlUtil.query(sql, [item._id + ""], function (err, result) {
                data[i] = item;
                if (result != null && result.length > 0) {
                    data[i]._doc.totalValue = result[0].total_value;
                    data[i]._doc.currentValue = result[0].current_value;
                }
                logger.info(i);
                i++;
                if (length == i) {
                    logger.info(data);
                    callback(err, data);
                }
            });
        });
    })
}

exports.getCommunityScoreList = function (map, callback) {

    Users.find({
        community: map.communityName
    }, 'nickName mobilePhone roomUnit roomNumber community').populate('community', 'communityName').limit(10).skip(Number((map.page - 1) * 10)).exec(function (err, user) {
        logger.info(user);
        var i = 0, length = user.length, data = new Array(length), data1 = {};
        //user.forEach(function(item){
        //    var sql = 'SELECT total_value,current_value FROM score_info WHERE user_id=?';
        //    mysqlUtil.query(sql, [item._id+""], function (err, result) {
        //        data[i] = item;
        //        if (result != null && result.length > 0) {
        //            data[i]._doc.totalValue = result[0].total_value;
        //            data[i]._doc.currentValue = result[0].current_value;
        //        }
        //        logger.info(i);
        //        i++;
        //        if(length == i){
        //            Users.find({
        //                community: map.communityName
        //            }).count().exec(function(err, count){
        //                logger.info("count "+count);
        //                data1 = {
        //                    data: data,
        //                    pages: count
        //                }
        //                logger.info(data1);callback(err, data1);
        //            });
        //        }
        //    });
        //});

        async.each(user, function (item, callbackInner) {
            mysqlUtil.query('SELECT total_value,current_value FROM score_info WHERE user_id=?', [item._id + ""], function (err, result) {
                logger.info(item);
                data[i] = item;
                if (result != null && result.length > 0) {
                    data[i]._doc.totalValue = result[0].total_value;
                    data[i]._doc.currentValue = result[0].current_value;
                }
                i++;
                if (length == i) {
                    Users.find({
                        community: map.communityName
                    }).count().exec(function (err, count) {
                        logger.info("count " + count);
                        data1 = {
                            data: data,
                            pages: count
                        }
                        logger.info(data1);
                        callback(err, data1);
                    });
                }
                //callbackInner()
            });
        }, function (err) {
            //data
        })
    })
};

exports.searchExchangeGoods = function (map, callback) {
    var sql = "SELECT score_exchange_record.id,score_exchange_record.remark,''as nickName,score_shop.product_name,score_exchange_record.exchange_num,score_exchange_record.product_price,score_exchange_record.user_name,score_exchange_record.mobile,score_exchange_record.address,score_exchange_record.status,score_exchange_record.create_time,score_exchange_record.user_id FROM score_shop,score_exchange_record WHERE score_shop.id = score_exchange_record.product_id ";
    //if (map.name != "" && map.status == null) {
    //    sql += " score_shop.product_name like '%" + map.name + "%' order by score_exchange_record.create_time DESC";
    //} else if (map.name == "" && map.status != null) {
    //    sql += " score_exchange_record.status = " + map.status + " order by score_exchange_record.create_time DESC";
    //} else {
    //    sql += " score_shop.product_name like '%" + map.name + "%'" + "and score_exchange_record.status = " + map.status + " order by score_exchange_record.create_time DESC";
    //}
    if (map.name != null){
        sql += " and score_shop.product_name like '%" + map.name + "%'";
    }
    if (map.status != null) {
        sql += " and score_exchange_record.status = " + map.status ;
    }
    if (map.username != null) {
        sql += " and score_exchange_record.user_name like '%" + map.username + "%'";
    }

    sql += " order by score_exchange_record.create_time DESC";
    sql += " limit " + (map.pageNum-1) * 20 +",20";

    logger.debug(sql);

    mysqlUtil.query(sql, function (err, result) {
        var length = result.length, i = 0;
        if (length == 0) {
            callback(err, result);
            return;
        }
        async.each(result, function (item, callbackInner) {
            //logger.info(item.user_id);
            Users.findOne({
                _id: item.user_id
            }, 'nickName community').populate('community', 'communityName').exec(function (err, user) {
                //logger.info(user);
                if(user && user.community){
                    item.communityName = user.community.communityName;
                }else{
                    item.communityName = "";
                }
                item.nickName = user.nickName;
                callbackInner();
            })
        }, function (err) {
            callback(err, result);
        })
    });
};

exports.searchExchangeGoodsCount = function (map, callback) {
    var sql = "SELECT count(*)as pages FROM score_shop,score_exchange_record WHERE score_shop.id = score_exchange_record.product_id ";

    if (map.name != null && map.name != ""){
        sql += " and score_shop.product_name like '%" + map.name + "%'";
    };
    if (map.status != null ) {
        sql += " and score_exchange_record.status = " + map.status ;
    };
    if (map.username != null) {
        sql += " and score_exchange_record.user_name like '%" + map.username + "%'";
    };
    logger.debug(sql);

    mysqlUtil.query(sql, function (err, result) {
        var length = result.length, i = 0;
        if (length == 0) {
            callback(err, result);
            return;
        }else{
            callback(null,result);
        }

    });
};

exports.getUserScoreList = function (userId, callback) {
    var sql = "SELECT change_type, change_value, change_reason, CASE when update_time > 10000000000 then update_time else update_time * 1000 end as time FROM score_sn WHERE user_id=? order by time desc";
    mysqlUtil.query(sql, [userId + ""], function (err, result) {
        var length = result.length, i = 0;
        if (length == 0) {
            callback(err, result);
            return;
        }
        async.each(result, function (item, callbackInner) {
            logger.info(item);
            if(item.change_type == 0){
                item.change_type = '获得';
                item.change_value = '+' + item.change_value;
            }else{
                item.change_type = '消耗';
                //item.change_value = '-' + item.change_value;
            }
            item.time = new Date(item.time).Format("yyyy-MM-dd hh:mm:ss");

            callbackInner();
        }, function (err) {
            callback(err, result);
        })
    });
};

exports.getAllCommunityStats = function (communityId, startdate, enddate, callback) {

    var startdatecondition = ""
    var enddatecondition = ""
    if(startdate != 'all')
        startdatecondition += " and CASE when update_time > 10000000000 then from_unixtime(update_time / 1000) else from_unixtime(update_time) end >='" + startdate + "'"
    if(enddate != 'all')
        enddatecondition += " and CASE when update_time > 10000000000 then from_unixtime(update_time / 1000) else from_unixtime(update_time) end <='" + enddate + "'"

    var commId = communityId + ""
    async.series([
        function (callbackInner){
            var sql = "SELECT communityName FROM 66xiaoqu_dwh.community where _id='" + commId + "'"
            mysqlUtil.query(sql, function(err, result){
                if(result[0] == undefined || result[0] == null)
                    callbackInner(err, commId)
                else
                    callbackInner(err, result[0].communityName)
            })
        },
        function (callbackInner){
            var sql = "SELECT sum(change_value) scoreGained FROM 66xiaoqu_db.score_sn as score"
            sql += " inner join 66xiaoqu_dwh.users as users on score.user_id = users._id WHERE community='" + commId + "'";
            sql += " and change_value > 0"
            sql += startdatecondition + enddatecondition
            mysqlUtil.query(sql, function(err, result){
                if(result[0] == undefined || result[0] == null)
                    callbackInner(err, 0)
                else
                    callbackInner(err, result[0].scoreGained)
            })
        },
        function (callbackInner){
            var sql = "SELECT sum(change_value) scoreConsumed FROM 66xiaoqu_db.score_sn as score"
            sql += " inner join 66xiaoqu_dwh.users as users on score.user_id = users._id WHERE community='" + commId + "'";
            sql += " and change_value < 0"
            sql += startdatecondition + enddatecondition
            mysqlUtil.query(sql, function(err, result){
                if(result[0] == undefined || result[0] == null)
                    callbackInner(err, 0)
                else
                    callbackInner(err, result[0].scoreConsumed)
            })
        },
        function (callbackInner){
            var sql = "select sum(after_current_value) currentvalue from (select * from"
            sql += "(SELECT user_id, after_current_value, CASE when update_time > 10000000000 then from_unixtime(update_time / 1000) else from_unixtime(update_time) end as time FROM 66xiaoqu_db.score_sn as score"
            sql += " inner join 66xiaoqu_dwh.users as users on score.user_id = users._id WHERE community='" + commId + "'";
            //sql += startdatecondition + enddatecondition
            sql += " order by time desc) as t group by user_id) as u"
            mysqlUtil.query(sql, function(err, result){
                if(result[0] == undefined || result[0] == null)
                    callbackInner(err, 0)
                else
                    callbackInner(err, result[0].currentvalue)
            })
        }
    ], callback);

    //Users.find({
    //    community: communityId + ""
    //}, 'nickname community').populate('community', 'communityName').exec(function (err, user) {
    //    var i = 0, length = user.length, data = [];
    //    if(length == 0){
    //        callback(err, null);
    //        return;
    //    }
    //    async.each(user, function (item, callbackInner) {
    //        var sql = "SELECT change_value,after_current_value, CASE when update_time > 10000000000 then update_time else update_time * 1000 end as time FROM score_sn WHERE user_id='" + item._id + "'";
    //        sql += " order by time desc";
    //
    //        mysqlUtil.query(sql, function (err, result) {
    //            i++;
    //            if (result != null && result.length > 0) {
    //                var details = [];
    //                result.forEach(function (value){
    //                    value.time = new Date(value.time).Format("yyyy-MM-dd hh:mm:ss");
    //                    details.push(value);
    //                });
    //                var data1 = {
    //                    user_id : item._id,
    //                    //username : item.nickname,
    //                    communityID : item.community._id,
    //                    communityName : item.community.communityName,
    //                    scoreRecord : details
    //                };
    //                data.push(data1);
    //            }
    //            if (length == i) {
    //                //logger.info(data.length);
    //                callback(err, data);
    //            }
    //        });
    //    }, function (err) {
    //        //callback(err, result);
    //    })
    //});

};